import os

import pandas as pd

from configs.db_connector import ProvincialPlatformConfigDev
from utils.db_config_utils import JdbcConfigUtils
from utils.json_utils import load_json


def clean_cell(cell):
    """清理单元格内容：去除前后空格和换行符"""
    if isinstance(cell, str):  # 确保只对字符串类型的单元格进行操作
        return cell.strip().replace('\n', ' ').replace('\r', ' ')
    return cell


def school_check(_result, _config):
    _dir = '学校信息校验'
    _save_path = os.path.join(_result, _dir)
    if not os.path.exists(_save_path):
        os.makedirs(_save_path, exist_ok=True)

    # 学校名称在同一个区县中不会重复
    _name_check_sql = f"""
        SELECT
            city,
            district,
            school_name,
            count(*) num
        FROM
            sunshine_basic_school_basic_info
        GROUP BY
            city,
            district,
            school_name
        HAVING
            count(*) > 1;
    """
    _name_check_df = JdbcConfigUtils.get_table_data(_config, _name_check_sql)
    if _name_check_df.shape[0] > 0:
        _condition_list = []
        for _index, _r in _name_check_df.iterrows():
            _condition_list.append(f"""
                        (district = '{_r['district']}'
                        AND school_name = '{_r['school_name']}')
                    """)
        _name_check_data_sql = f"""
            SELECT
                *
            FROM
                sunshine_basic_school_basic_info
            where 
               {' OR '.join(_condition_list)}
            order by district, school_name
        """
        _name_check_data_df = JdbcConfigUtils.get_table_data(_config, _name_check_data_sql)
        _name_check_data_df.to_excel(os.path.join(_save_path, '重复的学校名称.xlsx'), index=False)
    # 学校编号不会重复
    _code_check_sql = f"""
        SELECT
            school_code,
            count(*) num
        FROM
            sunshine_basic_school_basic_info
        GROUP BY
            school_code
        HAVING
            count(*) > 1;
    """
    _code_check_df = JdbcConfigUtils.get_table_data(_config, _code_check_sql)
    if _code_check_df.shape[0] > 0:
        _condition_list = []
        for _index, _r in _code_check_df.iterrows():
            _condition_list.append(f"""
                        (school_code = '{_r['school_code']}')
                    """)
        _code_check_data_sql = f"""
            SELECT
                *
            FROM
                sunshine_basic_school_basic_info
            where 
               {' OR '.join(_condition_list)}
            order by district
        """
        _code_check_data_df = JdbcConfigUtils.get_table_data(_config, _code_check_data_sql)
        _code_check_data_df.to_excel(os.path.join(_save_path, '重复的学校编号.xlsx'), index=False)
    # 和学校上报表对比
    _check_sql = f"""
        SELECT DISTINCT 
            city,
            district,
            school_name
        FROM
            sunshine_basic_school_basic_info;
    """
    _check_df = JdbcConfigUtils.get_table_data(_config, _check_sql)
    _file_path = './data/学校名称汇总v2.xlsx'
    _上报学校_df = pd.read_excel(_file_path)
    _上报学校_df = _上报学校_df[[
        '市',
        '县',
        '学校名称'
    ]]
    # 清理数据
    _check_df = _check_df.applymap(clean_cell)
    _上报学校_df = _上报学校_df.applymap(clean_cell)
    # 重命名列以统一字段名
    _check_df.rename(columns={
        'city': '市',
        'district': '县',
        'school_name': '学校名称'
    }, inplace=True)
    # 找出 _check_df 中存在但不在 _上报学校_df 中的数据
    diff_df = _check_df.merge(
        _上报学校_df,
        on=['市', '县', '学校名称'],
        how='left',
        indicator=True
    ).query('_merge == "left_only"').drop(columns=['_merge'])
    if diff_df.shape[0] > 0:
        diff_df = diff_df.sort_values(by=['市', '县', '学校名称'], ascending=[False, False, False]).reset_index(drop=True)
        diff_df.to_excel(os.path.join(_save_path, '多出的学校.xlsx'), index=False)


def school_canteen_check(_result, _config):
    _dir = '学校食堂信息校验'
    _save_path = os.path.join(_result, _dir)
    if not os.path.exists(_save_path):
        os.makedirs(_save_path, exist_ok=True)
    # 学校编号和学校名称是必填值
    _data_check_sql = f"""
        SELECT
            *
        FROM
            sunshine_basic_school_canteen_info
        where
            LENGTH(IFNULL(school_code, '')) = 0
            OR LENGTH(IFNULL(school_name, '')) = 0;
    """
    _data_check_df = JdbcConfigUtils.get_table_data(_config, _data_check_sql)
    if _data_check_df.shape[0] > 0:
        _data_check_df.to_excel(os.path.join(_save_path, '学校编号或者学校名称为空.xlsx'), index=False)

    # 学校食堂名称在同一个学校中不会重复
    _name_check_sql = f"""
        SELECT
            school_code,
            school_name,
            canteen_name,
            count(*) num
        FROM
            sunshine_basic_school_canteen_info
        GROUP BY
            school_code,
            school_name,
            canteen_name
        HAVING
            count(*) > 1;
    """
    _name_check_df = JdbcConfigUtils.get_table_data(_config, _name_check_sql)
    if _name_check_df.shape[0] > 0:
        _condition_list = []
        for _index, _r in _name_check_df.iterrows():
            _condition_list.append(f"""
                        (
                        school_code = '{_r['school_code']}'
                        AND school_name = '{_r['school_name']}'
                        AND canteen_name = '{_r['canteen_name']}'
                        )
                    """)
        _name_check_data_sql = f"""
            SELECT
                *
            FROM
                sunshine_basic_school_canteen_info
            where 
               {' OR '.join(_condition_list)}
            order by school_code, school_name, canteen_name
        """
        _name_check_data_df = JdbcConfigUtils.get_table_data(_config, _name_check_data_sql)
        _name_check_data_df.to_excel(os.path.join(_save_path, '重复的学校食堂名称.xlsx'), index=False)
    # 学校编号不会重复
    _code_check_sql = f"""
        SELECT
            school_code,
            canteen_code,
            count(*) num
        FROM
            sunshine_basic_school_canteen_info
        GROUP BY
            school_code,
            canteen_code
        HAVING
            count(*) > 1;
    """
    _code_check_df = JdbcConfigUtils.get_table_data(_config, _code_check_sql)
    if _code_check_df.shape[0] > 0:
        _condition_list = []
        for _index, _r in _code_check_df.iterrows():
            _condition_list.append(f"""
                        (
                        school_code = '{_r['school_code']}'
                        AND canteen_code = '{_r['canteen_code']}'
                        )
                    """)
        _code_check_data_sql = f"""
            SELECT
                *
            FROM
                sunshine_basic_school_canteen_info
            where 
               {' OR '.join(_condition_list)}
            order by district
        """
        _code_check_data_df = JdbcConfigUtils.get_table_data(_config, _code_check_data_sql)
        _code_check_data_df.to_excel(os.path.join(_save_path, '重复的学校食堂编号.xlsx'), index=False)

    # 和学校上报表对比
    _check_sql = f"""
        SELECT DISTINCT 
            city,
            district,
            school_name
        FROM
            sunshine_basic_school_canteen_info;
    """
    _check_df = JdbcConfigUtils.get_table_data(_config, _check_sql)
    _file_path = './data/学校名称汇总v2.xlsx'
    _上报学校_df = pd.read_excel(_file_path)
    _上报学校_df = _上报学校_df[[
        '市',
        '县',
        '学校名称'
    ]]
    # 清理数据
    _check_df = _check_df.applymap(clean_cell)
    _上报学校_df = _上报学校_df.applymap(clean_cell)
    # 重命名列以统一字段名
    _check_df.rename(columns={
        'city': '市',
        'district': '县',
        'school_name': '学校名称'
    }, inplace=True)
    # 找出 _check_df 中存在但不在 _上报学校_df 中的数据
    diff_df = _check_df.merge(
        _上报学校_df,
        on=['市', '县', '学校名称'],
        how='left',
        indicator=True
    ).query('_merge == "left_only"').drop(columns=['_merge'])
    if diff_df.shape[0] > 0:
        diff_df = diff_df.sort_values(by=['市', '县', '学校名称'], ascending=[False, False, False]).reset_index(drop=True)
        diff_df.to_excel(os.path.join(_save_path, '多出的学校.xlsx'), index=False)


if __name__ == '__main__':
    _data = 'data'
    _result = '学校食堂有效性校验'
    _config = ProvincialPlatformConfigDev()

    _table_json = load_json(os.path.join(_data, '校验数据表.json'))

    # 校验学校信息表中的信息是否重复
    school_check(_result, _config)
    # 校验学校食堂信息中食堂信息是否重复
    school_canteen_check(_result, _config)

